﻿using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Ribbon;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.AccessControl;
using System.Text;
using System.Windows.Forms;
using DataTable = System.Data.DataTable;

namespace 铸造温度处理2
{
    public partial class Ribbon1
    {
        Microsoft.Office.Tools.CustomTaskPane customPane;
        // public static string LuZi = "";
        public static List<string> validValues = new List<string> { "A", "B", "C", "D", "E", "F", "G", "H", "浇注2", "浇注1" }; // 或使用数组：new[] { "A", "B", "C" }

        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
            try
            {
                //var app = Globals.ThisAddIn.Application;
                //string workbookName = Globals.ThisAddIn.Application?.ActiveWorkbook?.Name;
                ////Microsoft.Office.Interop.Excel.Worksheet activeSheet =
                ////  Globals.ThisAddIn.Application.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
                //// 取第一个字符（如果 workbookName 不为空）
                //if (!string.IsNullOrEmpty(workbookName))
                //{
                //    LuZi = workbookName.Substring(0, 1); // 方法1：使用 Substring
                //                                         // LuZi = workbookName[0].ToString(); // 方法2：直接索引（需确保非空）
                //                                         // 初始化任务窗格
                //    //if (!validValues.Contains(LuZi))
                //    //{
                //    //    Editer editForm = new Editer();
                //    //    customPane = Globals.ThisAddIn.CustomTaskPanes.Add(editForm, "铸造温度处理2");
                //    //    customPane.DockPosition =  Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionLeft;
                //    //    customPane.Height = 70;
                //    //    customPane.Visible = true;
                //    //}
                //}
                //else
                //{
                //    LuZi = ""; // 如果 workbookName 为空，赋默认值
                //}

            }
            catch (Exception ex)
            {
                MessageBox.Show($"初始化失败: {ex.Message}{ex.StackTrace}");
            }
        }

        //private void button1_Click(object sender, RibbonControlEventArgs e)
        //{
        //    try
        //    {
        //        Globals.ThisAddIn.CustomTaskPanes[0].Visible = true;
        //       // System.Windows.Forms.MessageBox.Show(Globals.ThisAddIn.CustomTaskPanes[0].Height.ToString());

        //    }
        //    catch (Exception ex)
        //    {
        //        System.Windows.Forms.MessageBox.Show("出现异常" + ex.Message);
        //    }

        //}
        /// <summary>
        /// 解锁单元格,确保可以编辑
        /// </summary>
        private void UnprotectWorksheet()
        {
            try
            {
                Microsoft.Office.Interop.Excel.Worksheet activeSheet =
                    Globals.ThisAddIn.Application.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
                if (activeSheet == null)
                {
                    MessageBox.Show("没有活动的工作表！");
                    return;
                }
                activeSheet.Unprotect(); // 无密码时直接调用
                                         // activeSheet.Unprotect("yourpassword"); // 如果保护时有密码
                                         // 2. 可选：解锁所有单元格（确保所有单元格可编辑）
                activeSheet.Cells.Locked = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"解锁失败: {ex.Message}");
            }
        }
        /// <summary>
        /// 锁定第一行
        /// </summary>
        private void ProtectHeaderRow()
        {

            try
            {
                var activeSheet = Globals.ThisAddIn.Application.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
                if (activeSheet == null)
                {
                    MessageBox.Show("没有活动的工作表！");
                    return;
                }

                // 1. 解锁所有单元格（默认所有单元格是锁定的）
                activeSheet.Cells.Locked = false;

                // 2. 仅锁定第一行（标题行）
                Microsoft.Office.Interop.Excel.Range headerRow = activeSheet.Rows[1];
                headerRow.Locked = true;

                // 3. 保护工作表时，开放所有操作权限
                activeSheet.Protect(
                    Password: Type.Missing,  // 不设密码
                    AllowFormattingCells: true,     // 允许格式化单元格
                    AllowFormattingRows: true,     // 允许调整行高
                    AllowFormattingColumns: true,  // 允许调整列宽
                    AllowInsertingRows: true,      // 允许插入行
                    AllowInsertingColumns: true,    // 允许插入列
                    AllowDeletingRows: true,       // 允许删除行
                    AllowDeletingColumns: true,    // 允许删除列
                    AllowSorting: true,            // 允许排序
                    AllowFiltering: true           // 允许筛选
                );
            }
            catch (Exception ex)
            {
                MessageBox.Show($"出错: {ex.Message}");
            }
        }
        string LuZi = string.Empty;
        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                LuZi = Globals.ThisAddIn.Application?.ActiveWorkbook?.Name?.Substring(0, 1);
                if (!validValues.Contains(LuZi))
                {
                    LuZi = Globals.ThisAddIn.Application?.ActiveWorkbook?.Name?.Substring(0, 3);
                    if (!validValues.Contains(LuZi))
                    {
                        MessageBox.Show($"炉子识别错误,将默认A炉数据");
                        LuZi = "浇注1";
                    } 
                    
                }
                string sql = string.Empty;
                if (LuZi.ToLower().StartsWith("浇注"))
                {
                    sql = $@"SELECT CreateDate 日期,
       MuBan 模板号,
       PinMing 品名,
       PiHao 批号,
       LuBie 炉别,
       TongCi 桶次,StartTime 首温时间,EndTime 末温时间,StartTemp 首温温度,EndTemp 末温温度,StartStatus 首温结果,EndStatus 末温结果,Descri 备注
FROM dbo.DevJiaoZu
WHERE LuBie='{LuZi}' AND StartTime BETWEEN '{DateTime.Today}' AND '{DateTime.Now.AddHours(1)}'
ORDER BY StartTime ASC
";
                }
                else
                {
                    sql = $@"SELECT CreateDate 日期,
       MuBan 模板号,
       PinMing 品名,
       PiHao 批号,
       LuBie 炉别,
       TongCi 桶次,
       StartTime 测温时间,
       StartTemp 温度,
       StartStatus 结果
       FROM dbo.DevRongJie 
WHERE LuBie='{LuZi}' AND StartTime BETWEEN '{DateTime.Today}' AND '{DateTime.Now.AddHours(1)}'
ORDER BY StartTime ASC
";
                }
                UnprotectWorksheet();
                // 1. 获取当前活动的 Excel 工作表
                Microsoft.Office.Interop.Excel.Worksheet activeSheet =
                    Globals.ThisAddIn.Application.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
                if (activeSheet == null)
                {
                    MessageBox.Show("没有活动的工作表！");
                    return;
                }
                // 2. 清空当前工作表的所有数据（保留格式）
                ClearWorksheet(activeSheet);
                // 3. 获取数据
                var data = DbHelper.ExecuteDataset("server=10.0.2.136;database=DevData;uid=sa;pwd=Ql-mes-2021;Max Pool Size=3000;", CommandType.Text, sql);
                DataTable dataTable = data.Tables[0];

                // 4. 将 DataTable 写入工作表
                WriteDataTableToWorksheet(dataTable, activeSheet);

                ProtectHeaderRow();

            }
            catch (Exception ex)
            {
                MessageBox.Show($"出错: {ex.Message}");
            }
        }

        // 清空工作表数据（保留格式）
        private void ClearWorksheet(Microsoft.Office.Interop.Excel.Worksheet worksheet)
        {
            // 清除所有单元格的值和公式（保留格式）
            worksheet.Cells.ClearContents();
            // 清除格式
            // worksheet.Cells.Clear();
        }

        // 生成示例 DataTable（替换为你的实际数据）
        private DataTable GetSampleDataTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));

            dt.Rows.Add(1, "张三", 25);
            dt.Rows.Add(2, "李四", 30);
            dt.Rows.Add(3, "王五", 28);

            return dt;
        }

        private void WriteDataTableToWorksheet(DataTable dataTable, Microsoft.Office.Interop.Excel.Worksheet worksheet)
        {
            // 1. 写入数据
            for (int col = 0; col < dataTable.Columns.Count; col++)
            {
                // 写入列名
                worksheet.Cells[1, col + 1] = dataTable.Columns[col].ColumnName;

                // 写入数据
                for (int row = 0; row < dataTable.Rows.Count; row++)
                {
                    worksheet.Cells[row + 2, col + 1] = dataTable.Rows[row][col];
                }
            }

            // 2. 为"结果"列设置整列验证（包括未来新增行）

            if (LuZi.ToLower().StartsWith("浇注"))
            {
                SetColumnValidation(worksheet, dataTable, "首温结果");
                SetColumnValidation(worksheet, dataTable, "末温结果");
            }
            else
            {
                SetColumnValidation(worksheet, dataTable, "结果");
            }
            // 3. 自动调整列宽（可选）
            worksheet.Columns.AutoFit();
        }
        private void SetColumnValidation(Microsoft.Office.Interop.Excel.Worksheet worksheet,
                                  System.Data.DataTable dataTable, string columnName)
        {
            try
            {
                // 1. 检查列是否存在
                if (!dataTable.Columns.Contains(columnName)) return;
                int colIndex = dataTable.Columns.IndexOf(columnName) + 1;

                // 2. 定义数据范围（包括标题）
                Microsoft.Office.Interop.Excel.Range dataRange = worksheet.Range[
                    worksheet.Cells[1, 1],
                    worksheet.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]
                ];

                // 3. 转换为表格（如果不存在）
                Microsoft.Office.Interop.Excel.ListObject table;
                if (worksheet.ListObjects.Count == 0)
                {
                    table = worksheet.ListObjects.AddEx(
                        SourceType: XlListObjectSourceType.xlSrcRange,
                        Source: dataRange,
                        XlListObjectHasHeaders: XlYesNoGuess.xlYes
                    );
                    table.Name = "DataTable_" + DateTime.Now.ToString("yyyyMMddHHmmss");
                }
                else
                {
                    table = worksheet.ListObjects[1]; // 获取第一个表格
                }

                // 4. 设置验证规则
                Microsoft.Office.Interop.Excel.ListColumn targetColumn = table.ListColumns[colIndex];
                Microsoft.Office.Interop.Excel.Range columnRange = targetColumn.Range;

                columnRange.Validation.Delete(); // 清除旧验证

                columnRange.Validation.Add(
                    Type: XlDVType.xlValidateList,
                    AlertStyle: XlDVAlertStyle.xlValidAlertStop,
                    Operator: XlFormatConditionOperator.xlBetween,
                    Formula1: "OK,NG"
                );

                // 5. 可选设置
                columnRange.Validation.InputMessage = "请输入OK或NG";
                columnRange.Validation.IgnoreBlank = true;
                columnRange.Validation.InCellDropdown = true;

                // 6. 添加条件格式：如果单元格为 "NG"（不区分大小写），背景变红
                // 注意：条件格式需应用在数据区域（不包括标题）
                Microsoft.Office.Interop.Excel.Range dataOnlyRange = columnRange.Offset[1, 0].Resize[columnRange.Rows.Count - 1, 1]; // 排除标题行

                // 清除已有的条件格式，避免重复
                dataOnlyRange.FormatConditions.Delete();

                // 添加条件格式：等于 "NG" 或 "ng"
                Microsoft.Office.Interop.Excel.FormatCondition condition = (Microsoft.Office.Interop.Excel.FormatCondition)
                    dataOnlyRange.FormatConditions.Add(
                        Type: XlFormatConditionType.xlTextString,
                        String: "NG",
                        TextOperator: XlContainsOperator.xlContains
                    );

                // 设置格式：背景红色
                // condition.Interior.Color = System.Drawing.Color.Red.ToArgb();
                // 红色：OleColor (注意：BGR 而不是 RGB)
                condition.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                // 可选：设置字体为白色以提高可读性
                // condition.Font.Color = System.Drawing.Color.White.ToArgb();

                // 7. 自动扩展设置（关键）
                table.AutoFilter.ShowAllData(); // 确保筛选不会影响验证
                table.Range.AutoFormat();       // 刷新格式
            }
            catch (Exception ex)
            {
                MessageBox.Show($"表格验证设置失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 解析类容并上传
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click_1(object sender, RibbonControlEventArgs e)
        {
            try
            {
                // 1. 获取当前活动的 Excel 工作表
                Microsoft.Office.Interop.Excel.Worksheet activeSheet =
                    Globals.ThisAddIn.Application.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

                if (activeSheet == null)
                {
                    MessageBox.Show("没有活动的工作表！");
                    return;
                }
                // 2. 将工作表数据转换为 DataTable
                string json = string.Empty;
                if (LuZi.ToLower().StartsWith("浇注"))
                {
                    var data = ConvertWorksheetToDataTableDevJiaoZhu(activeSheet);
                    json = JsonConvert.SerializeObject(data);
                }
                else
                {
                    var data = ConvertWorksheetToDataTable(activeSheet);
                    json = JsonConvert.SerializeObject(data);
                }

                SqlParameter sqlParameter = new SqlParameter("Json", json);
                SqlParameter returnParam = new SqlParameter("@ReturnValue", SqlDbType.Int);
                returnParam.Direction = ParameterDirection.ReturnValue;
                if (LuZi.ToLower().StartsWith("浇注"))
                {
                    DbHelper.ExecuteNonQuery("server=10.0.2.136;database=DevData;uid=sa;pwd=Ql-mes-2021;Max Pool Size=3000;", CommandType.StoredProcedure, "UpdateJiaoZhuTempByManual", sqlParameter);
                }
                else
                {
                    DbHelper.ExecuteNonQuery("server=10.0.2.136;database=DevData;uid=sa;pwd=Ql-mes-2021;Max Pool Size=3000;", CommandType.StoredProcedure, "UpdateRongJieTempByManual", sqlParameter);
                }

                MessageBox.Show($"{(Convert.ToInt32(returnParam.Value) >= 0 ? "更新成功" : "失败,检查时间格式,数字格式是否都正确,然后再次重试")}");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"保存失败,出错: {ex.Message}");
            }
        }

        // 将 Excel 工作表数据转换为 DataTable
        private List<DevRongJie> ConvertWorksheetToDataTable(Microsoft.Office.Interop.Excel.Worksheet worksheet)
        {
            //DataTable dataTable = new DataTable();
            List<DevRongJie> list = new List<DevRongJie>();
            // 获取已使用的数据范围
            Microsoft.Office.Interop.Excel.Range usedRange = worksheet.UsedRange;

            // 读取数据到二维数组（提升性能）
            object[,] dataArray = (object[,])usedRange.Value;
            if (dataArray == null)
            {
                throw new Exception("没有数据哇");
            }
            // 第一行为列名
            int rowCount = dataArray.GetLength(0);
            int colCount = dataArray.GetLength(1);

            //// 添加列（假设第一行是列名）
            //for (int col = 1; col <= colCount; col++)
            //{
            //    string columnName = dataArray[1, col]?.ToString() ?? $"Column{col}";
            //    dataTable.Columns.Add(columnName);
            //}

            // 添加数据行（从第二行开始）
            for (int row = 2; row <= rowCount; row++)
            {
                DevRongJie dataRow = new DevRongJie();
                dataRow.CreateDate = Convert.ToDateTime(dataArray[row, 1]?.ToString());
                if (dataRow.CreateDate.Year < 2000)
                {
                    throw new Exception($"时间格式不对,第{row}行");
                }
                dataRow.MuBan = dataArray[row, 2]?.ToString() ?? string.Empty;
                dataRow.PinMing = dataArray[row, 3]?.ToString() ?? string.Empty;
                dataRow.PiHao = dataArray[row, 4]?.ToString() ?? string.Empty;
                dataRow.LuBie = dataArray[row, 5]?.ToString() ?? string.Empty;
                dataRow.TongCi = dataArray[row, 6]?.ToString() ?? string.Empty;
                dataRow.StartTime = Convert.ToDateTime(dataArray[row, 7]?.ToString());
                dataRow.StartTemp = Convert.ToDouble(dataArray[row, 8]?.ToString());

                var res = dataArray[row, 9]?.ToString()?.ToUpper() ?? string.Empty;
                if (res != "OK")
                {
                    res = "NG";
                }
                dataRow.StartStatus = res;
                list.Add(dataRow);
            }
            return list;
        }

        private List<DevJiaoZhu> ConvertWorksheetToDataTableDevJiaoZhu(Microsoft.Office.Interop.Excel.Worksheet worksheet)
        {
            //DataTable dataTable = new DataTable();
            List<DevJiaoZhu> list = new List<DevJiaoZhu>();
            // 获取已使用的数据范围
            Microsoft.Office.Interop.Excel.Range usedRange = worksheet.UsedRange;

            // 先读取数据到二维数组（提升性能）
            object[,] dataArray = (object[,])usedRange.Value;
            if (dataArray == null)
            {
                throw new Exception("没有数据哇");
            }
            // 第一行为列名
            int rowCount = dataArray.GetLength(0);
            int colCount = dataArray.GetLength(1);

            //// 添加列（假设第一行是列名）
            //for (int col = 1; col <= colCount; col++)
            //{
            //    string columnName = dataArray[1, col]?.ToString() ?? $"Column{col}";
            //    dataTable.Columns.Add(columnName);
            //}

            // 添加数据行（从第二行开始）
            for (int row = 2; row <= rowCount; row++)
            {
                DevJiaoZhu dataRow = new DevJiaoZhu();
                if (DateTime.TryParse(dataArray[row, 1]?.ToString(), out DateTime ct))
                {
                    if (ct.Year > 2000)
                    {
                        dataRow.CreateDate = ct.Date;
                    }
                    else
                    {
                        throw new Exception($"时间格式不对,第{row}行");
                    }
                }
                else
                {
                    throw new Exception($"时间格式不对,第{row}行");
                }

                dataRow.MuBan = dataArray[row, 2]?.ToString() ?? string.Empty;
                dataRow.PinMing = dataArray[row, 3]?.ToString() ?? string.Empty;
                dataRow.PiHao = dataArray[row, 4]?.ToString() ?? string.Empty;
                dataRow.LuBie = dataArray[row, 5]?.ToString() ?? string.Empty;
                dataRow.TongCi = dataArray[row, 6]?.ToString() ?? string.Empty;
                if (DateTime.TryParse(dataArray[row, 7]?.ToString(), out DateTime st))
                {
                    if (st.Year > 2000)
                    {
                        dataRow.StartTime = st;
                    }
                }
                if (DateTime.TryParse(dataArray[row, 8]?.ToString(), out DateTime et))
                {
                    if (et.Year > 2000)
                    {
                        dataRow.EndTime = et;
                    }
                }
                dataRow.StartTemp = Convert.ToDouble(dataArray[row, 9]?.ToString());
                dataRow.EndTemp = Convert.ToDouble(dataArray[row, 10]?.ToString());
                dataRow.StartStatus = dataArray[row, 11]?.ToString();
                dataRow.EndStatus = dataArray[row, 12]?.ToString();
                dataRow.Descri = dataArray[row, 13]?.ToString();

                //var res = dataArray[row, 9]?.ToString()?.ToUpper() ?? string.Empty;
                //if (res != "OK")
                //{
                //    res = "NG";
                //}
                // dataRow.StartStatus = res;
                list.Add(dataRow);
            }
            return list;
        }
    }


    public class DevRongJie
    {
        /// <summary>
        /// 日期
        /// </summary>
        public DateTime CreateDate { get; set; }

        /// <summary>
        /// 模板号
        /// </summary>
        public string MuBan { get; set; }

        /// <summary>
        /// 品名
        /// </summary>
        public string PinMing { get; set; }

        /// <summary>
        /// 批号
        /// </summary>
        public string PiHao { get; set; }

        /// <summary>
        /// 炉别
        /// </summary>
        public string LuBie { get; set; }

        /// <summary>
        /// 桶次
        /// </summary>
        public string TongCi { get; set; }

        /// <summary>
        /// 测温时间
        /// </summary>
        public DateTime StartTime { get; set; } = new DateTime(2000, 1, 1);

        /// <summary>
        /// 温度
        /// </summary>
        public double StartTemp { get; set; }

        /// <summary>
        /// 结果
        /// </summary>
        public string StartStatus { get; set; }
    }
    public class DevJiaoZhu
    {
        /// <summary>
        /// 日期
        /// </summary>
        public DateTime CreateDate { get; set; }

        /// <summary>
        /// 模板号
        /// </summary>
        public string MuBan { get; set; }

        /// <summary>
        /// 品名
        /// </summary>
        public string PinMing { get; set; }

        /// <summary>
        /// 批号
        /// </summary>
        public string PiHao { get; set; }

        /// <summary>
        /// 炉别
        /// </summary>
        public string LuBie { get; set; }

        /// <summary>
        /// 桶次
        /// </summary>
        public string TongCi { get; set; }

        /// <summary>
        /// 测温时间
        /// </summary>
        public DateTime StartTime { get; set; } = new DateTime(2000, 1, 1);

        /// <summary>
        /// 温度
        /// </summary>
        public double StartTemp { get; set; }

        /// <summary>
        /// 结果
        /// </summary>
        public string StartStatus { get; set; }

        /// <summary>
        /// 测温时间
        /// </summary>
        public DateTime EndTime { get; set; } = new DateTime(2000, 1, 1);

        /// <summary>
        /// 温度
        /// </summary>
        public double EndTemp { get; set; }

        /// <summary>
        /// 结果
        /// </summary>
        public string EndStatus { get; set; }

        /// <summary>
        /// 备注描述
        /// </summary>
        public string Descri { get; set; }
    }

}
